
/**
 * 
 */
package com.ucom;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.LinkedList;

/**
 * @author tathagata
 * 
 */
public class UcomWorkloadDifference {

	/**
	 * 
	 */
	private Connection dbConnection;
	private String query;
	private ResultSet rslt;
	private Statement stmtGetListEntries;

	public UcomWorkloadDifference() {
		try {
			Class.forName("org.apache.derby.jdbc.ClientDriver");
			dbConnection = DriverManager.getConnection(
					"jdbc:derby://localhost:1527/UCOM");
			stmtGetListEntries = dbConnection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

		} catch (ClassNotFoundException cnfe) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(cnfe.getMessage());

		} catch (SQLException sqle) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(sqle.getMessage());
		}

	}

	public LinkedList<Query9> query9() {
		LinkedList<Query9> result = new LinkedList<Query9>();
		try {
			
			query = "SELECT X.USER_ID,Y.USER_ID, Y.WORKLOADU- X.WORKLOADG"
			+ " FROM" 
			+ " (SELECT COUNT(COMMAND)AS WORKLOADU, TRANSCRIPT.USER_ID,COURSE_INFO.GRADUATE_LEVEL"
			+ " FROM COURSE_INFO,TRANSCRIPT,USAGE_HISTORY"
			+ " WHERE COURSE_INFO.COURSE_ID= TRANSCRIPT.COURSE_ID"
			+ " AND USAGE_HISTORY.USER_ID= TRANSCRIPT.USER_ID"
			+ " and COURSE_INFO.GRADUATE_LEVEL = 0"
			+ " GROUP BY TRANSCRIPT.USER_ID,COURSE_INFO.GRADUATE_LEVEL )Y,"
			+ " (SELECT COUNT(COMMAND) AS WORKLOADG, TRANSCRIPT.USER_ID,COURSE_INFO.GRADUATE_LEVEL"
			+ " FROM COURSE_INFO,TRANSCRIPT,USAGE_HISTORY"
			+ " WHERE COURSE_INFO.COURSE_ID= TRANSCRIPT.COURSE_ID"
			+ " AND USAGE_HISTORY.USER_ID= TRANSCRIPT.USER_ID"
			+ " and COURSE_INFO.GRADUATE_LEVEL = 1"
			+ " GROUP BY TRANSCRIPT.USER_ID,COURSE_INFO.GRADUATE_LEVEL )X"
			+ " WHERE X.USER_ID != Y.USER_ID" ;

			rslt = stmtGetListEntries.executeQuery(query);

			while (rslt.next()) {
				System.out.print(rslt.getString(1));
				System.out.print("\t" + rslt.getString(2));
				System.out.println("\t" + rslt.getInt(3));
				result.add(new Query9(rslt.getString(1), rslt.getString(2), rslt.getInt(3)));
			}
			rslt.close();
		} catch (SQLException sqle) {
			System.out.println("Error in getting transaction details");
			System.out.println(sqle.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
}
